package com.hz.ys_production.dbsql;



import com.hz.framework.MyInterFace.SystemInterFace;

/**
 * *****************************************************************************
 *               Copyright(c) 2010 by 哈尔滨华泽数码科技有限公司.
 *                       All rights reserved.
 *******************************************************************************
 *     File Name         :  com.hz.ys_stock.dbsql.StockSql.java
 *     Description(说明)	:  库存管理 sql 语句
 * -----------------------------------------------------------------------------
 *     No.        Date              Revised by           Description	
 *     	1	  Jun 10, 2011           韩飞              Created
 *****************************************************************************
 */
public class DailyPaperSql implements SystemInterFace {
	

//	查询生产日报信息
	public static final String SEL_DAILYPAPER = "select d.*,t.*,p.machinename from pres_dailypaper d "
				+" left join  taskInfo t  on t.taskid= d.taskid"
				+" left join machinetask m on t.taskid= m.taskid"
				+" left join pres_machineinfo p on p.machineid = m.machineid where 1=1 ";
	
//	根据id查询材料信息
	public static final String SEL_DAILYPAPER_INFO = "select * from pres_dailypaper where id = ?";
	
//	删除生产报告信息
	public static final String DEL_DAILYPAPER_INFO = "delete from pres_dailypaper where taskid = ?";
	
//	根据生产单
	public static final String SEL_TASKINFO_INFO = "select t.taskid,t.taskname,m.createtime,m.finishtime,m.createuser from taskinfo t left join machinetask m on t.taskid = m.taskid where t.taskid = ?";
	
//	更新
	public static final String UPD_MACHINETASK_INFO ="UPDATE machinetask SET status = ?,finishtime = ? where taskid = ?";
	public static final String UPD_TASK_INFO ="UPDATE taskinfo SET status = ? where taskid = ?";
	//补纸 更新库存数量
	public static final String UPD_STOCK_INFO = "update pres_stock p set numnow = numnow - ? , numreal= numreal - ?  where "
				+" exists (select t.goodsid from taskinfo t where p.goodsid = t.goodsid and t.taskid = ? )";
	
	//更新完工后的机台状态信息
	public static final String UPD_MACHINE_INFO ="update pres_machineinfo p  set p.status= ? where exists " 
				+"(select m.machineid from machinetask m where p.machineid = m.machineid and m.taskid = ? )"
				+" and not exists (select m.machineid from machinetask m where p.machineid = m.machineid and m.status = '已开工')";
	
	public static final String UPD_ORDER_INFO ="UPDATE orderinfo SET status = ? where orderid = ?";
	
	//查询对应生产单的订单下所有生产单的数量和完成的数量 ywg 已完工  sy 所有
	public static final String SEL_TASK_COM ="select ywg, sy ,o.orderid  from orderinfo o  "
				+" left join taskinfo t on t.orderid = o.orderid " 
				+" left join (select count(taskid) as ywg,orderid from taskinfo where status='已完工' group by orderid) e on e.orderid = o.orderid "
				+" left join (select count(taskid) as sy,orderid from taskinfo group by orderid) s on s.orderid = o.orderid"
				+" where t.taskid = ?";
	
	/** pres_dailypaper_new **/
//	根据生产单 id 查询相关订单详细信息
	public static final String SEL_TASKINFONEW_INFO = "select g.goodsname,g.standard,o.orderid,o.name,t.taskid,t.taskname,t.printsize,m.createtime,m.finishtime,m.createuser from taskinfo t " +
			" left join machinetask m on t.taskid = m.taskid" +
			" left join orderinfo o on t.orderid = o.orderid" +
			" left join pres_makings g on g.goodsid = t.goodsid" +
			" where t.taskid = ?";
//	根据id查询材料信息
	public static final String SEL_DAILYPAPERNEW_INFO = "select g.goodsname,g.standard,o.orderid,o.name,t.taskname,p.* from pres_dailypaper_new p " +
			" left join taskinfo t on t.taskid = p.taskid" +
			" left join orderinfo o on t.orderid = o.orderid" +
			" left join pres_makings g on g.goodsid = t.goodsid" +
			" where p.taskid = ?";
//	删除生产报告信息
	public static final String DEL_DAILYPAPERNEW_INFO = "delete from pres_dailypaper_new where taskid = ?";
//	查询生产日报信息
	public static final String SEL_DAILYPAPERNEW = "select o.orderid,o.name,t.*,d.*,p.machinename from pres_dailypaper_new d "
				+" left join taskInfo t  on t.taskid= d.taskid"
				+" left join machinetask m on t.taskid= m.taskid"
				+" left join orderinfo o on t.orderid = o.orderid"
				+" left join pres_machineinfo p on p.machineid = m.machineid where 1 = 1 ";
	
	//修改报告信息对补纸 更新库存数量
	public static final String UPD_STOCK_NUM = "update pres_stock p set numnow = (numnow + ?) - ? , numreal= (numreal+ ?) - ?  where "
				+" exists (select t.goodsid from taskinfo t where p.goodsid = t.goodsid and t.taskid = ? )";
	
}
